package com.pkuse.bjousystem.repository.school;

import com.pkuse.bjousystem.dto.school.GraduationRegistryStudentsDetailDTO;
import com.pkuse.bjousystem.dto.school.SchoolName;
import com.pkuse.bjousystem.dto.school.enrollment.EnrollmentDetailDTO;
import com.pkuse.bjousystem.entity.school.School;
import com.pkuse.bjousystem.repository.school.enrollment.EnrollmentRepository;
import com.pkuse.bjousystem.repository.school.graduation.GraduationRepository;
import com.pkuse.bjousystem.repository.school.registry.RegistryRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface SchoolRepository extends CrudRepository<School, String>, GraduationRepository, RegistryRepository, EnrollmentRepository {

    @Query("select new com.pkuse.bjousystem.dto.school.GraduationRegistryStudentsDetailDTO" +
            "(xsbyb.xh,xsb.xm,bjxxb.bmc,xsb.nd,rxxqdmb.xqmc,xsbyb.bynd,byxqdmb.xqmc,zydmb.zymc,xslbdmb.xslbmc,xsbyb.dzzczh,xsjbqkb.brlxdh,xsjbqkb.yddh) " +
            "from XueShengBiYe xsbyb inner join Student xsb on (xsbyb.xh=xsb.xh and xsb.xxdm=:xxdm and xsbyb.bynd=:bynd and xsbyb.byxq=:byxq and xsb.zydm=:zydm) " +
            "inner join XueShengJiBenQingKuang xsjbqkb on xsbyb.xh=xsjbqkb.xh " +
            "inner join BanJiXinXi bjxxb on (bjxxb.bdm=xsb.bdm and bjxxb.xslbdm=:xslbdm) " +
            "inner join XueShengLeiBieDaiMa xslbdmb on xslbdmb.xslbdm=bjxxb.xslbdm " +
            "inner join ZhuanYeDaiMa zydmb on zydmb.zydm=xsb.zydm " +
            "inner join XueQiDaiMa rxxqdmb on rxxqdmb.xqdm=xsb.xqdm " +
            "inner join XueQiDaiMa byxqdmb on byxqdmb.xqdm=xsbyb.byxq")
    Page<GraduationRegistryStudentsDetailDTO> listGraduationDetails(
            @Param("xxdm") String xxdm,
            @Param("bynd") String bynd,
            @Param("byxq") String byxq,
            @Param("xslbdm") String xslbdm,
            @Param("zydm") String zydm,
            Pageable pageable);

    @Query("select new com.pkuse.bjousystem.dto.school.GraduationRegistryStudentsDetailDTO" +
            "(xsb.xh, xsb.xm, bjxxb.bmc, xsb.nd, rsxqdmb.xqmc, xsbyb.bynd, byxqdmb.xqmc, zydmb.zymc, xslbdmb.xslbmc, xsbyb.dzzczh, xsjbqkb.brlxdh, xsjbqkb.yddh) " +
            "from Student xsb  " +
            "left join XueShengJiBenQingKuang xsjbqkb on xsb.xh = xsjbqkb.xh " +
            "left join XueShengBiYe xsbyb on xsb.xh = xsbyb.xh " +
            "left join ZhuanYeDaiMa zydmb on xsb.zydm = zydmb.zydm " +
            "left join BanJiXinXi bjxxb on xsb.bdm = bjxxb.bdm " +
            "left join XueShengLeiBieDaiMa xslbdmb on bjxxb.xslbdm = xslbdmb.xslbdm " +
            "left join XueQiDaiMa rsxqdmb on xsb.xqdm = rsxqdmb.xqdm " +
            "left join XueQiDaiMa byxqdmb on xsbyb.byxq = byxqdmb.xqdm " +
            "where xsb.xh in  " +
            "( " +
            " select xsxkjgb.xh  " +
            " from XueShengXuanKeJieGuo xsxkjgb " +
            " inner join BanJiXinXi bjxxb on xsxkjgb.bdm = bjxxb.bdm " +
            " inner join XueShengLeiBieDaiMa xslbdmb on xslbdmb.xslbdm = bjxxb.xslbdm and xslbdmb.xslbdm = :xslbdm " +
            " inner join Student xsb on xsxkjgb.xh = xsb.xh " +
            " inner join ZhuanYeDaiMa zydmb on xsb.zydm = zydmb.zydm and zydmb.zydm = :zydm " +
            " where xsxkjgb.xxdm = :xxdm and xsxkjgb.pzh is not null and xsxkjgb.nd = :zcnd and xsxkjgb.xqdm = :zcxq " +
            " group by xsxkjgb.nd, xsxkjgb.xqdm, xsxkjgb.xh, xsxkjgb.xxdm, xsxkjgb.bdm " +
            ") ")
    Page<GraduationRegistryStudentsDetailDTO> listRegistryDetails(
            @Param("xxdm") String xxdm,
            @Param("zcnd") String zcnd,
            @Param("zcxq") String zcxq,
            @Param("xslbdm") String xslbdm,
            @Param("zydm") String zydm,
            Pageable pageable);

    @Query("select new com.pkuse.bjousystem.dto.school.enrollment.EnrollmentDetailDTO" +
            "(xsb.xh,xsb.xm,bjxxb.bmc,xsb.nd,xqdmb.xqmc,zydmb.zymc,xslbdmb.xslbmc,xsjbqkb.brlxdh,xsjbqkb.yddh) " +
            "from Student xsb " +
            "left join XueShengJiBenQingKuang xsjbqkb on xsb.xh=xsjbqkb.xh " +
            "left join XueQiDaiMa xqdmb on xqdmb.xqdm=xsb.xqdm " +
            "inner join BanJiXinXi bjxxb on bjxxb.bdm=xsb.bdm and bjxxb.xslbdm = :xslbdm " +
            "left join XueShengLeiBieDaiMa xslbdmb on xslbdmb.xslbdm=bjxxb.xslbdm " +
            "left join ZhuanYeDaiMa zydmb on zydmb.zydm=xsb.zydm " +
            "where xsb.xjztdm='1' " +
            "and xsb.xxdm=:xxdm " +
            "and xsb.nd=:rxnd " +
            "and xsb.xqdm=:rxxq " +
            "and xsb.zydm=:zydm")
    Page<EnrollmentDetailDTO> listEnrollmentDetails(
            @Param("xxdm") String xxdm,
            @Param("rxnd") String rxnd,
            @Param("rxxq") String rxxq,
            @Param("xslbdm") String xslbdm,
            @Param("zydm") String zydm,
            Pageable pageable);

    @Query("select distinct new com.pkuse.bjousystem.dto.school.SchoolName(xxdmb.xxmc, false) from School xxdmb")
    List<SchoolName> listXxmc();
}
